Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# AGGREGATION

The AGGREGATION function is available only within a model. Its purpose is to allow you to create a model that represents a custom aggregate. Such an aggmap can be used for dynamic aggregation with the AGGREGATE function.

Note: Because the AGGREGATION function is intended only for dynamic aggregation, a model that contains such a function cannot be used with the AGGREGATE command.

Syntax

AGGREGATION(dimval-list)

Arguments

dimval-list

  A list of one or more dimension values to include in the custom aggregation. The specified values must belong to the same dimension to which the target dimension value belongs. You must specify each dimension value as a text literal. That is, they cannot be represented by a text expression such as a variable.

Examples

Example 7-13 Using the AGGREGATION Function to Create a Custom Aggregate

The following lines of code from a program perform these steps:

1. Add the new dimension value `my_time` to the `time` dimension.

       MAINTAIN time ADD 'My_Time'

2. Define the model `mytime_custagg` and set the specification of the model using the AGGREGATION function.

       DEFINE mytime_custagg MODEL
       MODEL JOINLINES('DIMENSION time' 'My_Time = AGGREGATION(\'23\' \'24\')')

   (Note that backslash escape characters are required to include quotation marks within a quoted string.)

3. Define the `sales_aggmap` aggmap.

       DEFINE sales_aggmap AGGMAP <time cpc <customer product channel> >
       AGGMAP
       RELATION prntrel.time
       RELATION prntrel.chan
       RELATION prntrel.prod
       RELATION prntrel.cust
       END

4. Add the model `mytime_custagg` to `sales_aggmap`.

       AGGMAP ADD mytime_custagg TO sales_aggmap

5. Limit the dimensions to the values of interest and run a report.

       " Run a report
       LIMIT time TO 'My_Time' '23' '24'
       LIMIT channel TO '5'
       LIMIT product TO '70'
       LIMIT customer TO '114'
       REPORT DOWN time AGGREGATE(sales USING sales_aggmap)

   The report generates the following output.

       CHANNEL: 5
       PRODUCT: 70
                      --AGGREGATE(SALES---
                      USING SALES_AGGMAP)-
                      ------CUSTOMER------
       TIME                   114
       -------------- --------------------
       my_time                  682,904.34
       23                        84,982.92
       24                       597,921.42